package dao.impl;

import bean.User;
import bean.user_info;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import dao.UserDao;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import utils.c3p0Utils;

import javax.lang.model.element.QualifiedNameable;
import java.sql.Array;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;

/**
 * @Author guoxiuwei
 * @Date 2020/8/17 20:05
 * @Title
 */
public class UserDaoImpl implements UserDao {
    private static QueryRunner qr = new QueryRunner(c3p0Utils.getDataSource());
    @Override
    public boolean login(String name, String password) {



        String sql = "select * from user where uname = ? and upwd = PASSWORD(?) and isalive=1";
        Object[] query = new Object[0];


        try {
            query = qr.query(sql, new ArrayHandler(), name, password);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        //如果这个list里面有一个数据 证明就是有  并且账号密码都对
        //因为使用的是数组存储这个数据 所以会有5个长度 对应数据库里面的字段个数
        if(query.length == 5){
            return true;
        }
        return false;
    }

    @Override
    public boolean register(String name, String password, int rights) {

        /*
        注册之前要去数据库查询是否有这个用户账号了
        如果有了 直接返回
        没有才要继续插入
         */
        String findsql="select * from user where uname=?";
        User user=new User();
        try {
            user=qr.query(findsql,new BeanHandler<User>(User.class),name);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        if (user!=null){
            return false;
        }
        /*
        如果没有返回false 就证明现在要插入的用户名和原有数据不会重复
         */


        String sql = "insert into user values(null,?,PASSWORD(?),1,?)";
        int i = 0;
        try {
            i = qr.update(sql,name,password,rights);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        if (i == 1){
            return true;
        }
        return false;
    }

    @Override
    public int infoADD(int uinfoid, String rname, String gender, String phoneNumber, Date inductionTime, String address, double salary) {
        String sql="INSERT INTO user_info VALUES (?,?,?,?,?,?,?)";

        int i=0;
        try {
            i=qr.update(sql,uinfoid,rname,gender,phoneNumber,inductionTime,address,salary);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }

    @Override
    public User FindUid(String name) {
        String sql = "select * from user where uname=?";

        User user= null;
        try {
            user = qr.query(sql,new BeanHandler<User>(User.class),name);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return user;
    }

    /*
    * 分页功能，暂时有bug，使用MyBatis插件可以解决，sql语句自己写不会
    * */
    @Override
    public PageInfo InfoList(int page, int size) {
        String sql="SELECT * FROM user_info,user where user.uid=user_info.uinfoid AND isalive=1";

        PageHelper.startPage(page,size);

        List<user_info> list=null;
        try {
            list=qr.query(sql,new BeanListHandler<user_info>(user_info.class));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        PageInfo<user_info> pageinfo=new PageInfo<>(list);

        return pageinfo;
    }

    @Override
    public List<User> UserFind() {
        String sql="SELECT uid,uname,rights FROM user where isalive=1";
        List<User> list= null;
        try {
            list = qr.query(sql,new BeanListHandler<User>(User.class));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public User UserFindId(int uid) {
        String sql="SELECT uid,uname,rights FROM user where uid=?";
        User user= null;
        try {
            user = qr.query(sql,new BeanHandler<User>(User.class),uid);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return user;
    }

    @Override
    public int DeleteInfo(int uinfoid) {
        String sql="UPDATE user SET isalive=0 where uid=?";
        int i=0;
        try {
            i=qr.update(sql,uinfoid);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }

    @Override
    public user_info InfoUpdateFind(int uinfoid) {
        String sql="SELECT * FROM user_info WHERE uinfoid=?";
        PageHelper.startPage(1,1);
        user_info u= null;
        try {
            u = qr.query(sql,new BeanHandler<user_info>(user_info.class),uinfoid);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return u;
    }

    @Override
    public int InfoUpdate(int uinfoid, String rname, String gender, String phoneNumber, Date inductionTime, String address, double salary) {
        String sql="UPDATE user_info SET rname=?,gender=?,phoneNumber=?,inductionTime=?,address=?,salary=? where uinfoid=?";
        int i= 0;
        try {
            i = qr.update(sql,rname,gender,phoneNumber,inductionTime,address,salary,uinfoid);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }

    @Override
    public int PermissionUpdate(int uid, int rights) {
        String sql="UPDATE user SET rights=? where uid=?";
        int i= 0;
        try {
            i = qr.update(sql,rights,uid);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }

    @Override
    public PageInfo InfoSearch(String rname) {
        String sql="SELECT * FROM user_info,user WHERE user.uid=user_info.uinfoid AND isalive=1 AND rname LIKE ?";
        List<user_info> user= null;
        try {
            user = qr.query(sql,new BeanListHandler<user_info>(user_info.class),"%"+rname+"%");
        } catch (SQLException e) {
            e.printStackTrace();
        }

        PageInfo<user_info> pageinfo=new PageInfo<>(user);
        return pageinfo;
    }

    @Override
    public List<User> PermissionSearch(String uname) {
        String sql="SELECT * FROM user WHERE isalive=1 AND uname LIKE ?";
        List<User> user= null;
        try {
            user = qr.query(sql,new BeanListHandler<User>(User.class),"%"+uname+"%");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return user;
    }

    @Override
    public User personnelNum() {
        String sql="select COUNT(rights) sum ,rights FROM user GROUP BY rights";
        User user=new User();
        try {
            List<User> list=qr.query(sql,new BeanListHandler<User>(User.class));
            for (int i=0;i<list.size();i++){
                if (list.get(i).getRights()==1){
                    user.setAdminNum(list.get(i).getSum());
                }else if (list.get(i).getRights()==2){
                    user.setManager(list.get(i).getSum());
                }else if(list.get(i).getRights()==3){
                    user.setEmployess(list.get(i).getSum());
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return user;
    }

    @Override
    public boolean passwordUpdate(String uname, String oldPassword, String newPassword) {
        String sql1 = "select * from user where uname = ? and upwd = PASSWORD(?)";
        String sql2 = "UPDATE user SET upwd=password(?) WHERE uname = ?";
        Object[] qu = new Object[0];
        try {
            //验证原密码是否正确
            qu = qr.query(sql1, new ArrayHandler(),uname,oldPassword);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        //如果原密码不正确  直接返回false
        if (qu.length == 0){
            return false;
        }
        int i = 0;
        try {
            i = qr.update(sql2,newPassword,uname);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        if (i == 1){
            return true;
        }

        return false;
    }


}
